﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class ChildServices
    {
        private static Child GetChildFromDataReader(IDataReader dataReader)
        {
            Child retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Child();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                retVal.ChildId = dataReader["CHILD_ID"].ToString();
                retVal.Title = dataReader["TITLE"].ToString();
                retVal.Other_Names = dataReader["FULL_NAME"].ToString();
                retVal.Surname = dataReader["SURNAME"].ToString();
                retVal.FamilyName = dataReader["FAMILY_NAME"].ToString();
                retVal.CountryOfRes = dataReader["COUNTRY_RES"].ToString();
                retVal.Citizenship = dataReader["CITIZENSHIP"].ToString();
                DateTime dob = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["DOB"].ToString(), out dob))
                {
                    retVal.DoB = dob;
                }
                retVal.Gender = dataReader["GENDER"].ToString();
                retVal.MaritalStatus = dataReader["MARITAL_STATUS"].ToString();
                retVal.AddressRes = dataReader["ADDRESS_RES"].ToString();
                retVal.AddressOff = dataReader["ADDRESS_OFF"].ToString();
                retVal.TeleMob = dataReader["TELE_MOB"].ToString();
                retVal.TeleRes = dataReader["TELE_RES"].ToString();
                retVal.TeleOff = dataReader["TELE_OFF"].ToString();
                retVal.Fax = dataReader["FAX"].ToString();
                retVal.Email = dataReader["EMAIL"].ToString();
                retVal.Occupation = dataReader["OCCUPATION"].ToString();
                retVal.Remarks = dataReader["REMARKS"].ToString();                
            }
            return retVal;
        }

        public static Child GetChildByKeys(string client_id, string child_id)
        {
            Child retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Child where client_id = '" + client_id + "' and child_id = '" + child_id  + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetChildFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static ChildCollection GetChildrenByClientId(string client_id)
        {
            ChildCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Child where client_id = '" + client_id + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ChildCollection();
                    while (dataReader.Read())
                    {
                        Child item = GetChildFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static ChildCollection GetChildrenSumByClientId(string client_id)
        //{
        //    ChildCollection retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select Surname, FAMILY_NAME as \"Family name\", "+
        //                           "TiTLE || '.' || FULL_NAME as \"Full name \", "+
        //                           "ADDRESS_RES as Address,TELE_RES as \"Tele no.\", "+
        //                           "TELE_MOB as Mobile, DOB as \"Date of birth\", "+
        //                           "COUNTRY_RES as \"Country od Residence\", Citizenship "+
        //                           " from Child where client_id = '" + client_id + "'";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new ChildCollection();
        //            while (dataReader.Read())
        //            {
        //                Child item = GetChildFromDataReader(dataReader);
        //                if (item != null)
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}


        public static ChildCollection GetChildCollection()
        {
            ChildCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Child";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new ChildCollection();
                    while (dataReader.Read())
                    {
                        Child item = GetChildFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetChildCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from Child";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Child child)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into Child(CLIENT_ID, CHILD_ID,TITLE, FULL_NAME, DOB, GENDER, MARITAL_STATUS, ADDRESS_RES,ADDRESS_OFF," +
                                  "TELE_MOB, TELE_RES, TELE_OFF, FAX, EMAIL, OCCUPATION, REMARKS, SURNAME, FAMILY_NAME, COUNTRY_RES, CITIZENSHIP)" +
                                  "values(:v1,:v2,:v3,:v4,:v5,:v6,:v7,:v8,:v9,:v10,:v11,:v12,:v13,:v14,:v15,:v16,:v17,:v18,:v19,:v20 )";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = child.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = child.ChildId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = child.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = child.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = child.DoB;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = child.Gender;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = child.MaritalStatus;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = child.AddressRes;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = child.AddressOff;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = child.TeleMob;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = child.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = child.TeleOff;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = child.Fax;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = child.Email;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = child.Occupation;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = child.Remarks;

                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = child.Surname;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = child.FamilyName;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = child.CountryOfRes;
                cmd.Parameters.Add(":v20", OracleType.VarChar).Value = child.Citizenship;  

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string client_id, string child_id)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from child where client_id = '" + client_id + "' and child_id = '" + child_id + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Child child)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update child set " +
                                  "TITLE = :v3 ," +
                                  "FULL_NAME = :v4 ," +
                                  "DOB = :v5 ," +
                                  "GENDER = :v6 ," +
                                  "MARITAL_STATUS = :v7 ," +
                                  "ADDRESS_RES = :v8 ," +
                                  "ADDRESS_OFF = :v9 ," +
                                  "TELE_MOB = :v10 ," +
                                  "TELE_RES = :v11 ," +
                                  "TELE_OFF = :v12 ," +
                                  "FAX = :v13 ," +
                                  "EMAIL = :v14 ," +
                                  "OCCUPATION = :v15 ," +
                                  "REMARKS = :v16 " +
                                  "SURNAME = :v17 ," +
                                  "FAMILY_NAME = :v18 ," +
                                  "COUNTRY_RES = :v19 ," +
                                  "CITIZENSHIP = :v20 " +
                                  "where client_id = :v1 " +
                                  "and child_id = :v2";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = child.ClientId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = child.ChildId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = child.Title;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = child.Other_Names;
                cmd.Parameters.Add(":v5", OracleType.DateTime).Value = child.DoB;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = child.Gender;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = child.MaritalStatus;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = child.AddressRes;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = child.AddressOff;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = child.TeleMob;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = child.TeleRes;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = child.TeleOff;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = child.Fax;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = child.Email;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = child.Occupation;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = child.Remarks;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = child.Surname;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = child.FamilyName;
                cmd.Parameters.Add(":v19", OracleType.VarChar).Value = child.CountryOfRes;
                cmd.Parameters.Add(":v20", OracleType.VarChar).Value = child.Citizenship;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
